/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sgsmcan.Dao;

import com.sgsmcan.Entidad.Perro;
import com.sgsmcan.Entidad.Sereno;
import com.sgsmcan.Entidad.SerenoPerro;
import com.sgsmcan.Entidad.SerenoPerroId;
import com.sgsmcan.Hibernate.HibernateUtil;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author GOMEZ
 */
public class SerenoPerroImpl implements SerenoPerroDao {

    @Override
    public void insertarSerenoPerro(Sereno sereno, Perro perro) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            tx = session.beginTransaction();

            SerenoPerro sp = new SerenoPerro();
            sp.setSereno(sereno);
            sp.setPerro(perro);
            sp.setEstado(true);
            sp.setFechaasignacion(new Timestamp(new Date().getTime()));

            session.save(sp);
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();
            }
            //  Utilitario.LOG.error(CLASE + " Error al insertar ", e);
            throw new HibernateException(e);
        } finally {
            if (session != null) {
                session.close();
            }

        }
    }

    @Override
    public void eliminarSerenoPerro(SerenoPerro sp) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            tx = session.beginTransaction();
            sp.setEstado(false);
            session.update(sp);
            tx.commit();

        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();

            }

        } finally {
            if (session != null) {
                session.close();
            }
        }
    }

    @Override
    public DetachedCriteria getSerenoPerro() {

        DetachedCriteria criteria = DetachedCriteria.forClass(SerenoPerro.class, "serenoperro").add(Restrictions.eq("estado", true));

        return criteria;
    }

    @Override
    public SerenoPerro getSerenoPerroid(int serenoid, int perroid) {
        SerenoPerro sep = null;

        Session sesion = null;

        try {
            sesion = HibernateUtil.getSessionFactory().openSession();
            Query query = sesion.createQuery("SELECT SP FROM SerenoPerro as SP WHERE SP.spk.sereno.id = :s AND SP.spk.perro.id = :p");
            query.setParameter("s", serenoid);
            query.setParameter("p", perroid);
            sep = (SerenoPerro) query.uniqueResult();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            if (sesion != null) {
                sesion.close();
            }
        }

        return sep;
    }

    @Override
    public void actualizarSerenoPerro(SerenoPerro sp, Sereno sp2, Perro pe2) {
        Session s = null;
        Transaction tx = null;
        try {
            s = HibernateUtil.getSessionFactory().openSession();
            tx = s.beginTransaction();

            s.delete(sp);

            SerenoPerro sp_2 = new SerenoPerro();
            sp_2.setSereno(sp2);
            sp_2.setPerro(pe2);
            sp_2.setFechaasignacion(new Timestamp(new Date().getTime()));
            sp_2.setEstado(true);
            s.save(sp_2);
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();
            }
        } finally {
            if (s != null) {
                s.close();
            }
        }
    }

    @Override
    public void reasignar(Sereno sereno, Perro perro) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            tx = session.beginTransaction();

            SerenoPerro sp = (SerenoPerro) session.get(SerenoPerro.class, new SerenoPerroId(sereno, perro));

            sp.setEstado(true);
            sp.setFechaasignacion(new Timestamp(new Date().getTime()));

            session.update(sp);
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();
            }
            //  Utilitario.LOG.error(CLASE + " Error al insertar ", e);
            throw new HibernateException(e);
        } finally {
            if (session != null) {
                session.close();
            }

        }
    }

    @Override
    public List<SerenoPerro> getSerenoPerroDisponible(Timestamp start, Timestamp end) {
        List<SerenoPerro> lst = null;
        Session sesion = null;
        try {
            sesion = HibernateUtil.getSessionFactory().openSession();
            String sql = "SELECT sp FROM SerenoPerro as sp "
                       + "WHERE sp.estado = 1 AND sp NOT IN "
                       + "(SELECT esp.serenoPerro FROM EventoSerenoPerro as esp WHERE 1 = 1 "
                       + " AND ( (esp.evento.fechaEvento < :start AND esp.evento.fechaEventoFin > :end) "
                       + " OR    (esp.evento.fechaEvento > :start AND esp.evento.fechaEvento < :end)"
                       + " OR    (esp.evento.fechaEventoFin > :start AND esp.evento.fechaEventoFin < :end)"
                       + " OR    (esp.evento.fechaEvento = :start AND esp.evento.fechaEventoFin = :end) ))";
            Query query = sesion.createQuery(sql);
            query.setParameter("start", start);
            query.setParameter("end", end);
            lst = query.list();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            if (sesion != null) {
                sesion.close();
            }
        }
        return lst;
    }

    @Override
    public List<SerenoPerro> getSerenoPerroOcupado(Timestamp start, Timestamp end, int nidEvento) {
        List<SerenoPerro> lst = null;
        Session sesion = null;
        try {
            sesion = HibernateUtil.getSessionFactory().openSession();
            String sql = "SELECT esp.serenoPerro FROM EventoSerenoPerro as esp WHERE esp.evento.idEvento <> :nidEvento "
                       + " AND ( (esp.evento.fechaEvento < :start AND esp.evento.fechaEventoFin > :end) "
                       + " OR    (esp.evento.fechaEvento > :start AND esp.evento.fechaEvento < :end)"
                       + " OR    (esp.evento.fechaEventoFin > :start AND esp.evento.fechaEventoFin < :end)"
                       + " OR    (esp.evento.fechaEvento = :start AND esp.evento.fechaEventoFin = :end) )";
            Query query = sesion.createQuery(sql);
            query.setParameter("nidEvento", nidEvento);
            query.setParameter("start", start);
            query.setParameter("end", end);
            lst = query.list();
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            if (sesion != null) {
                sesion.close();
            }
        }
        return lst;
    }

}
